import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.lang.*;
//These two imports come from the classes111.zip
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;



public class DBOps
{
	public Connection con = null;
      public Statement stmt = null;
      int USERID; // this int is initialized when the user signs on/is validated, and is used in several DBOps methods

    	public DBOps()
	{
      	System.out.println("Connecting to the DB");
            try
		{
            	DriverManager.registerDriver(new OracleDriver());
                  String userid = "se17";
                  String pwd = "k16c15c2";
                  //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			String connectString ="jdbc:oracle:thin:@(description=(address=(host=ragnall.ee.pitt.edu)(protocol=tcp)(port=1521))(connect_data=(sid=j7)))";

                  //Connect to the database here
                  con = DriverManager.getConnection(connectString,userid,pwd);
                  con.setAutoCommit(true);
		}

            catch(Exception e)
		{
            	System.out.println("Error Connecting");
                  System.out.println(e);
                  System.exit(1);
 	      }

            System.out.println("Connected");

    	} // End of Public DBOps


      // works
    	public  void add_user(String usname, String login, String password)
	{

       	try
		{
        		stmt = con.createStatement();

                	// get the current largest value for the auto-increment column for the table
        		ResultSet rsMAX = stmt.executeQuery("SELECT MAX(USERID) FROM user_table");
        		rsMAX.next();   // make the cursor point to the first row in the ResultSet

       		// make an integer from the above query, and add 1 to it so we know what value to assign the next time we add a record to the table
        		int startWith = rsMAX.getInt("MAX(USERID)") + 1;

                	// create the sequence object so that Oracle can do an auto-increment
                	//stmt.executeQuery("CREATE SEQUENCE seq_add_user MINVALUE 1 START WITH " + startWith + " INCREMENT BY 1 CACHE 10");

                  stmt.executeUpdate("INSERT INTO USER_TABLE VALUES (" + startWith + ", '" + usname + "', '" + login + "', '" + password + "', 'N')");

		} // End Try

		catch(SQLException sqle)
		{
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
            }



    	} // End of add_user

    	// works
    	//might want to change the section number from a String to an int

	public  void add_course(String department, int number, String section, int class_number, String verbose)
	{
      	try
		{
        		stmt = con.createStatement();

                  // get the current largest value for the auto-increment column for the table
                  ResultSet rsMAX = stmt.executeQuery("SELECT MAX(CID) FROM course_table");
                  rsMAX.next();   // make the cursor point to the first row in the ResultSet

                        // make an integer from the above query, and add 1 to it so we know what value to assign the next time we add a record to the table
                        int startWith = rsMAX.getInt("MAX(CID)") + 1;

                        stmt.executeUpdate("INSERT INTO COURSE_TABLE VALUES (" + startWith + ", '" + department + "'," + number + ", '" + section + "', " + class_number + ", '" + verbose + "')");
        	}

		catch(SQLException sqle)
		{
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
            }

    	} // End of Add course

    	// works
 	public  void add_user_course_link(int userid, int class_number, int usid, String surveystatus)
	{
      	try
		{
        		stmt = con.createStatement();
                	// get the current largest value for the auto-increment column for the table
                  ResultSet rsMAX = stmt.executeQuery("SELECT MAX(UCLID) FROM user_course_link");
                  rsMAX.next();   // make the cursor point to the first row in the ResultSet

                  // make an integer from the above query, and add 1 to it so we know what value to assign the next time we add a record to the table
               	int startWith = rsMAX.getInt("MAX(UCLID)") + 1;

            	// create the sequence object so that Oracle can do an auto-increment
                	//stmt.executeQuery("CREATE SEQUENCE seq_add_user MINVALUE 1 START WITH " + startWith + " INCREMENT BY 1 CACHE 10");
				String c_number = new String(Integer.toString(class_number));
                  stmt.executeUpdate("INSERT INTO USER_COURSE_LINK VALUES (" + startWith + ", " +  userid + ", (select cid from course_table where class_number='" + c_number + "'), " + usid + ", '" + surveystatus + "')");
        	}						
         	catch(SQLException sqle)
		{
              	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
             }

    	}

    	// works
    	public  void add_question_type(int qtid, String verbose)
	{
      	try
		{
        		stmt = con.createStatement();
                  stmt.executeUpdate("INSERT INTO QUESTION_TYPE VALUES ("+ qtid +", '" + verbose + "')");
        	}
            catch(SQLException sqle)
		{
                  System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
            }
    	} // End of add_question_type

    	// works
    	public  void add_user_status(int usid, String status)
	{
      	try
		{
      		stmt = con.createStatement();
            	stmt.executeUpdate("INSERT INTO USER_STATUS VALUES (" + usid + ", '" + status + "')");
      	}
      	catch(SQLException sqle)
		{
       		System.out.println("Unsuccessful Execution");
            	System.out.println(sqle);
      	}
    	}


  	// works
      // returns 1 for valid user, 0 for invalid user, and -1 if there is an error

    	public  int validateUser(String login, String password)
	{
      	try
		{
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  ResultSet rs = stmt.executeQuery("SELECT LOGIN, PASSWORD, USERID FROM USER_TABLE WHERE LOGIN = '" + login + "' AND PASSWORD = '" + password + "'");

                  if(rs.first())  //user is validated
                  {
                  	USERID = rs.getInt("USERID");
                        //System.out.println("USERID: " + USERID);

                        return 1;
                  }

                  else
                  {
                       	return 0;
                  }


         	} // end try

            catch(SQLException sqle)
		{
                  System.out.println("Unsuccessful Execution");
            	System.out.println(sqle);

               	return -1;
            }



      } // end validate user

      // works
       public int getUserID(String login)
	        {
	  		  try
	  		  		{
	  		              	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
	                    ResultSet rs = stmt.executeQuery("SELECT USERID FROM user_table WHERE login = '" + login + "'");
	                    rs.first();
	                    return rs.getInt("USERID");

	       	}	// end try


	  			   catch(SQLException sqle)
	  			  		{
	  			                    System.out.println("Unsuccessful Execution");
			              	System.out.println(sqle);
			              	return -1;
						}

			}	// end getuserId


			public int getAdminStatus(String login)
				  {
					  try
					  {
					  stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
					                    ResultSet rs = stmt.executeQuery("SELECT admin FROM user_table WHERE login = '" + login + "'");
					                    rs.first();
					                    String admin = rs.getString("admin");

					                    if(admin.equals("Y"))
					                    	return 1;

					                    else return 0;

					  			  }	// end try

					  			   catch(SQLException sqle)
					  			  		{
					  			                    System.out.println("Unsuccessful Execution");
					  			              	System.out.println(sqle);

					  			                 	return -1;
			            }
	  }	// end getAdminStatus


      // works
      public  int changePassword(String login, String oldPassword, String newPassword)
     	{
      	try
		{
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  //ResultSet rs = stmt.executeQuery("SELECT LOGIN, PASSWORD FROM USER_TABLE WHERE USER = '" + login + "' AND PASSWORD = '" + password + "'");
                  if(validateUser(login, oldPassword) == 1)
                  {
                  	stmt.executeQuery("UPDATE USER_TABLE SET PASSWORD = '" + newPassword + "' WHERE LOGIN = '" + login + "'");
                        return 1;
                  }

                  else    return 0;



        	}

         	catch(SQLException sqle)
		{
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
                  return -1;
           	}

     	}// end change password


     	public void closeConnection()
     	{
      	//disconnecting
            System.out.println("Disconnecting from the DB");
            try
		{
            	//Disconnect from the database here
                  con.close();
            }
            catch(SQLException sqle)
		{
 	           System.out.println("Error Disconnecting");
                 System.out.println(sqle);
            }
        }

	//works, might have to alter to return the instructor(s) of a course
      public Course[] getCourses(int userid)
	{
      	try
		{
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  ResultSet rs = stmt.executeQuery("SELECT COURSE_TABLE.CID, COURSE_TABLE.DEPARTMENT, COURSE_TABLE.COURSE_NUMBER, COURSE_TABLE.SECTION, COURSE_TABLE.CLASS_NUMBER, COURSE_TABLE.VERBOSE, USER_COURSE_LINK.USID FROM COURSE_TABLE INNER JOIN USER_COURSE_LINK ON COURSE_TABLE.CID = USER_COURSE_LINK.CID WHERE USERID = " + userid + "AND USER_COURSE_LINK.SURVEYSTATUS = 'N'");

                  ArrayList<Course> courses = new ArrayList<Course>();

                  while(rs.next())
                  {
            		//courses.add(new Course("", "", rs.getString(0), rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)));
                        courses.add(new Course(rs.getInt("CID"), "", "", rs.getString("DEPARTMENT"), rs.getString("COURSE_NUMBER"), rs.getString("SECTION"), rs.getString("CLASS_NUMBER"), rs.getString("VERBOSE"), rs.getInt("USID")));
                  }

              	//System.out.println("number of course objects (should be 7): " + courses.size());
                  Course courseArray[] = new Course[courses.size()];      // create empty array of Courses
                  courseArray = courses.toArray(courseArray);     // populate the array of Courses

                	/*
                 	for(int i = 0; i < courses.size(); i++)
        	      {
                  	System.out.println("course " + i + ":\n");
                        System.out.println(courseArray[i].department + " | " + courseArray[i].courseNumber + " | " + courseArray[i].section + " | " + courseArray[i].classNumber + " | " + courseArray[i].verbose + " | " + courseArray[i].status +"\n");
                  }
                  */

                  return courseArray;

 		} // end try

            catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                 	System.out.println(sqle);
                  //return -1;
            }

            Course[] nullset = new Course[1];
            return nullset;
	} // end Get Course

      // works
     	public Question[] getSurveyQuestions(int cid)
      {
      	try
           	{
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  ResultSet rs = stmt.executeQuery("SELECT QID, QUESTION, QTYPE, USERID, RESPONSEA, RESPONSEB, RESPONSEC, RESPONSED, RESPONSEE FROM SURVEY_QUESTION WHERE CID = 0 OR CID = " + cid);

                  ArrayList<Question> questions = new ArrayList<Question>();
                  while(rs.next())
                  {
                 		Question q = new Question();
						q.setQid(rs.getInt("QID"));
                        q.setContent(rs.getString("QUESTION"));
                        q.setType(rs.getInt("QTYPE"));
						if(rs.getString("USERID") == null)
				{
                  		q.setDefault(true);
                		}
				else
				{
                  		q.setDefault(false);
                		}

				String[] answers = {rs.getString("RESPONSEA"), rs.getString("RESPONSEB"), rs.getString("RESPONSEC"), rs.getString("RESPONSED"), rs.getString("RESPONSEE")};
                        q.setPossibleAns(answers);

                        questions.add(q);

                      	// for debugging
                       	//System.out.println("content: " + q.getContent());
                        //System.out.println("type: " + q.getType());
                        //System.out.println("possible answers: " + q.getPossibleAns()[0] + " | " + q.getPossibleAns()[1] + " | " + q.getPossibleAns()[2] + " | " + q.getPossibleAns()[3] + " | " + q.getPossibleAns()[4]);
                	}

                 	Question questionArray[] = new Question[questions.size()];      // create empty array of Questions
                  questionArray = questions.toArray(questionArray);       // populate the array of Questions

                  return questionArray;




         	} // end try

            catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                	System.out.println(sqle);
            }

            Question[] nullset = new Question[1];
            return nullset;
	} // End of getSurveyQuestions


     	// do we need to pass in the userid?  could use this method to alter the survey status field from "N" to "Y"
      // not sure if the Response class is correct.  If not, then this method will definitely need to be altered
	  // amm - Dave, I changed the the sql queries to use responses table instead of results table,
	  //   and fixed the insert statement to handle null values
      public int submitSurvey(int userid, int cid, Response[] responseToQuestions)
      {
      	try
            {
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                	for(int i = 0; i < responseToQuestions.length; i++)
                	{
                  	// get the current largest value for the auto-increment column for the table
                        ResultSet rsMAX = stmt.executeQuery("SELECT MAX(SRID) FROM RESPONSES");
                        rsMAX.next();   	// make the cursor point to the first row in the ResultSet
                                    	// make an integer from the above query, and add 1 to it so we know what value to assign the next time we add a record to the table
        			int startWith = rsMAX.getInt("MAX(SRID)") + 1;

						// amm - added code to handle null values
						int qid = -1;
						String resp = null;
						if (responseToQuestions[i] != null)
						{
							qid = responseToQuestions[i].id;
							resp = responseToQuestions[i].resp;
						}

                        stmt.executeUpdate("INSERT INTO RESPONSES VALUES (" + startWith + ", " + cid + ", " + qid + ", '" + resp + "')");
                	}

                	stmt.executeUpdate("UPDATE user_course_link SET SURVEYSTATUS = 'Y' WHERE CID = " + cid + " AND USERID = " + userid);

                	return 1;

           	} //end try

            catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
                  return -1;
            }
 	}       // end submitSurvey


     	//works
      public int addSurveyQuestion(int userid, int cid, Question q)
      {
      	try
  	      {
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  // get the current largest value for the auto-increment column for the table
                  ResultSet rsMAX = stmt.executeQuery("SELECT MAX(QID) FROM survey_question");
                  rsMAX.next();   // make the cursor point to the first row in the ResultSet
                                  // make an integer from the above query, and add 1 to it so we know what value to assign the next time we add a record to the table

			int startWith = rsMAX.getInt("MAX(QID)") + 1;

                	if(q.getType() == 2) // is a multiple choice question
                	{
             		stmt.executeQuery("INSERT INTO SURVEY_QUESTION VALUES (" + startWith + ", '" + q.getContent() + "', 2, " + cid + ", " + userid + ", '" + q.getPossibleAns()[0] + "', '" + q.getPossibleAns()[1] + "', '" + q.getPossibleAns()[2] + "', '" + q.getPossibleAns()[3] + "', '" + q.getPossibleAns()[4] + "')");
                	}

                	else if(q.getType() == 3)
                	{
                  	//String debug = "INSERT INTO SURVEY_QUESTION VALUES (" + startWith + ", '" + q.getContent() + "', 3, " + cid + ", " + userid + ", '" + q.getPossibleAns()[0] + "', '" + q.getPossibleAns()[1] + "', '" + q.getPossibleAns()[2] + "', '" + q.getPossibleAns()[3] + "', '" + q.getPossibleAns()[4] + "')";
                        //System.out.println("debug: " + debug);
                        stmt.executeQuery("INSERT INTO SURVEY_QUESTION (QID, QUESTION, QTYPE, CID, USERID) VALUES (" + startWith + ", '" + q.getContent() + "', 3, " + cid + ", " + userid + ")");// + ", '" + q.getPossibleAns()[0] + "', '" + q.getPossibleAns()[1] + "', '" + q.getPossibleAns()[2] + "', '" + q.getPossibleAns()[3] + "', '" + q.getPossibleAns()[4] + "')");
                	}

                	else if(q.getType() == 1)
                	{
                  	stmt.executeQuery("INSERT INTO SURVEY_QUESTION VALUES (" + startWith + ", '" + q.getContent() + "', 1, " + cid + ", " + userid + ", '" + q.getPossibleAns()[0] + "', '" + q.getPossibleAns()[1] + "', '" + q.getPossibleAns()[2] + "', '" + q.getPossibleAns()[3] + "', '" + q.getPossibleAns()[4] + "')");
                	}

                	return 1;
        	} // end try

        	catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
                  return -1;
            }
	} // End of add survey question

	public int removeSurveyQuestion(int qid)
      {
      	try
  	      {
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  // get the current largest value for the auto-increment column for the table
                  
                	
             		stmt.executeQuery("DELETE FROM SURVEY_QUESTION WHERE QID = " + qid);
                	return 1;
        	} // end try

        	catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);
                  return -1;
            }
	} // End of remove survey question
	
      // returns all results of a particular course's survey
      // not clear if this is what we want
      public Response[] getSurveyResults(int cid)
      {
      	try
            {
            	stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                  //ResultSet rs = stmt.executeQuery("SELECT OPEN_ENDED_RESULTS.RESULTS FROM OPEN_ENDED_RESULTS INNER JOIN SURVEY_QUESTION ON OPEN_ENDED_RESULTS.CID = SURVEY_QUESTION.CID AND SURVEY_QUESTION.QID = OPEN_ENDED_RESULTS.QID");
                  //amm - I modified this query to retrieve responses, not results
				  ResultSet rs = stmt.executeQuery("SELECT qid, responses FROM responses WHERE cid = " + cid + " AND qid != -1 ORDER BY qid ASC");

                	ArrayList<Response> responses = new ArrayList<Response>();

                  while(rs.next())
                  {
                  	Response r = new Response(rs.getString("responses"), rs.getInt("qid"));
                        responses.add(r);
                  }

                  Response responseArray[] = new Response[responses.size()];      // create empty array of Responses
                  responseArray = responses.toArray(responseArray);       // populate the array of Responses

                  return responseArray;

            } // end try

            catch(SQLException sqle)
            {
            	System.out.println("Unsuccessful Execution");
                  System.out.println(sqle);

		}

            Response[] nullset = new Response[1];
            return nullset;

	}// end getSurveyResults

	
    	/*public static void main(String[] args) {

                                new DBOps();

                                /*      methods that are commented out are ones that have been executed successfully. */
                           //add_course(1, "COE", 1186, "1070", 20892, "Software Engineering");
                           //add_course(2, "COE", 1186, "1080", 20894, "Software Engineering lab");
               //add_course(3, "ECE", 1150, "1030", 20878, "Intro to Computer Newtorks");
               //add_course(4, "MATH", 280, "1070", 15916, "Linear Algebra");   // won't accept '0280' for the course number
               //add_course(5, "PEDC", 0002, "1050", 11272, "Intermediate Swimming");
               //add_course(6, "ECE", 1192, "1070", 20906, "Intro to VLSI Design");
               //add_course(7, "ECE", 1192, "1080", 20908, "Intro to VLSI Design lab");
               //add_course(8, "COE", 1501, "1070", 20670, "Algorithm Implementation");
               //add_course(9, "COE", 1501, "1080", 20668, "Algorithm Implementation rec");
               //add_course(10, "ECE", 1885, "1030", 20896, "Departmental Seminar");
               //add_course(11, "ECE", 1896, "1010", 20898, "Senior Design Project");
               //add_course(12, "CS", 1550, "1040", 12602, "Intro to Operating Systems");
               //add_course(13, "CS", 1550, "1060", 13832, "Intro to Operating Systems rec");
               //add_course(14, "CS", 1571, "1070", 12832, "Intro to A.I.");
               //add_course(15, "CS", 1590, "1060", 12966, "Soc. Imp. of Comp. Tech.");



               //add_user(2, "dave", 920, "dac61", "penguin");

                                //add_user_course_link(1, 2, 123, 456, "N");

                                //add_question_type(1, "multiple_choice");

                                //add_user_status(111, "student");


                                /******* validateUser TEST *******/

                                //int validate1 = validateUser("dac61", "penguin");
                                //int validate2 = validateUser("bob", "cat");

                                //System.out.println("user dac61: " + validate1);
                                //System.out.println("user bob: " + validate2);

                                /***********************************/







       // }     // end main
} // end class


